package com.qingke.blog.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.qingke.blog.pojo.Article;
import com.qingke.blog.pojo.User;

public class BlogDAO {
	private String driverStr="com.mysql.jdbc.Driver";
	private String connStr="jdbc:mysql://localhost:3306/blog?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	private String sqlUsername="zjy";
	private String sqlPassword="123456";
	
    private Connection conn=null;		
    private String sql;
    private PreparedStatement ps=null;
    private ResultSet rs=null;  
    
    {
    	try {
			Class.forName(driverStr);
			conn = DriverManager.getConnection(connStr,sqlUsername,sqlPassword);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    }
    
    public boolean isUsernameExist(String username){
    	sql="select * from user where username=?";
    	try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,username);
			rs=ps.executeQuery();
			if(rs!=null&&rs.next()){
				return true;
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
    }
    
    public User login(String username,String password){
    	sql="select * from user where username=? and password=?";
    	try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,username);
			ps.setString(2,password);
			rs=ps.executeQuery();
			if(rs!=null&&rs.next()){
				User user=new User();
				user.setId(rs.getInt("id"));
				user.setUsername(username);
				user.setPassword(password);
				user.setNickname(rs.getString("nickname"));
				return user;
			}
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
    	
    }
    
    public boolean register(String username,String password,String nickname){
    	if(isUsernameExist(username)){
    		return false;
    	}
    	sql="insert into user(username,password,nickname) values(?,?,?);";
    	try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,username);
			ps.setString(2,password);
			ps.setString(3,nickname);
			int count=ps.executeUpdate();
			if(count>0)
				return true;
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
    }
    
    public boolean writerArticle(Article article){
    	try {
    		conn.setAutoCommit(false);
    		sql="insert into article(article_name,newstime,content_path,user_id) values(?,?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1,article.getArticleName());
			ps.setTimestamp(2,new Timestamp(article.getNewstime().getTime()));
			ps.setString(3,article.getContentPath());
			ps.setInt(4, article.getUserId());
			int count=ps.executeUpdate();
			conn.commit();
			if(count==0)
				return false;
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		}
    }
    public List<Article> listAllArticle(){
    	sql="select * from article";
    	List<Article> list=null;
    	try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs!=null){
				list=new ArrayList<>();
				while(rs.next()){
					Article a=new Article();
					a.setId(rs.getInt("id"));
					a.setArticleName(rs.getString("article_name"));
					a.setNewstime(rs.getTimestamp("newstime"));
					a.setContentPath(rs.getString("content_path"));
					a.setUserId(rs.getInt("user_id"));
					list.add(a);
				}
				if(list.size()==0)
					list=null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return list;
    }
    
    public boolean isLikeExist(int userId,int articleId){
    	sql="select * from `like` where user_id=? and article_id=?";
    	try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			ps.setInt(2,articleId);
			rs=ps.executeQuery();
			if(rs!=null&&rs.next()){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return false;
    }
    
    public boolean isDislikeExist(int userId,int articleId){
    	sql="select * from `dislike` where user_id=? and article_id=?";
    	try {
    		ps=conn.prepareStatement(sql);
    		ps.setInt(1,userId);
    		ps.setInt(2,articleId);
    		rs=ps.executeQuery();
    		if(rs!=null&&rs.next()){
    			return true;
    		}
    	} catch (SQLException e) {
    		e.printStackTrace();
    	}
    	return false;
    }
    
    public boolean insertLike(int userId,int articleId){
    	if(isLikeExist(userId,articleId)){
    		return false;
    	}
    	sql="insert into `like`(user_id,article_id) values(?,?);";
    	try {
    		ps=conn.prepareStatement(sql);
    		ps.setInt(1,userId);
    		ps.setInt(2,articleId);
    		int count=ps.executeUpdate();
    		if(count>0)
    			return true;
    	} catch (SQLException e) {
    		e.printStackTrace();
    	}
    	return false;
    }
    
    public boolean deleteLike(int userId,int articleId){
    	if(!isLikeExist(userId,articleId)){
    		return false;
    	}
    	sql="delete from `like` where user_id=? and article_id=?";
    	try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			ps.setInt(2,articleId);
			int count=ps.executeUpdate();
			if(count>0)
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return false;
    }
    
    public boolean insertDislike(int userId,int articleId){
    	if(isDislikeExist(userId,articleId)){
    		return false;
    	}
    	sql="insert into `dislike`(user_id,article_id) values(?,?);";
    	try {
    		ps=conn.prepareStatement(sql);
    		ps.setInt(1,userId);
    		ps.setInt(2,articleId);
    		int count=ps.executeUpdate();
    		if(count>0)
    			return true;
    	} catch (SQLException e) {
    		e.printStackTrace();
    	}
    	return false;
    }
    
    public boolean deleteDislike(int userId,int articleId){
    	if(!isDislikeExist(userId,articleId)){
    		return false;
    	}
    	sql="delete from `dislike` where user_id=? and article_id=?";
    	try {
    		ps=conn.prepareStatement(sql);
    		ps.setInt(1,userId);
    		ps.setInt(2,articleId);
    		int count=ps.executeUpdate();
    		if(count>0)
    			return true;
    	} catch (SQLException e) {
    		e.printStackTrace();
    	}
    	return false;
    }
    
    public Article selectArticleById(int articleId){
    	sql="select * from article where id=?";
    	Article article=null;
    	try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,articleId);
			rs=ps.executeQuery();
			if(rs!=null){
				if(rs.next()){
					article=new Article();
					article.setId(rs.getInt("id"));
					article.setArticleName(rs.getString("article_name"));
					article.setNewstime(rs.getTimestamp("newstime"));
					article.setContentPath(rs.getString("content_path"));
					article.setUserId(rs.getInt("user_id"));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return article;
    }
    
    public boolean updateArticleNameById(String articleName,int articleId){
    	sql="update article set article_name=? where id=?";
    	try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,articleName);
			ps.setInt(2,articleId);
			int count=ps.executeUpdate();
			if(count>0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return false;
    }
    
    public int selectLikeByArticleId(int articleId){
    	sql="select count(1) num from `like` where article_id=?";
    	int count=0;
    	try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,articleId);
			rs=ps.executeQuery();
			if(rs!=null&&rs.next()){
				count=rs.getInt("num");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return count;
    }
    
    public int selectDislikeByArticleId(int articleId){
    	sql="select count(1) num from `dislike` where article_id=?";
    	int count=0;
    	try {
    		ps=conn.prepareStatement(sql);
    		ps.setInt(1,articleId);
    		rs=ps.executeQuery();
    		if(rs!=null&&rs.next()){
    			count=rs.getInt("num");
    		}
    	} catch (SQLException e) {
    		e.printStackTrace();
    	}
    	return count;
    }
    
    public boolean editArticle(User user,Article article){
    	
    	return false;
    }
    public void close(){
    	try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	try {
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
}
